/*
 * Copyright (C) 2011 TopCoder Inc., All Rights Reserved.
 */
package gov.nasa.pds.services.impl;

import gov.nasa.pds.entities.Cell;
import gov.nasa.pds.entities.Column;
import gov.nasa.pds.entities.DataFile;
import gov.nasa.pds.entities.DataSet;
import gov.nasa.pds.entities.Instrument;
import gov.nasa.pds.entities.InstrumentHost;
import gov.nasa.pds.entities.MetadataObject;
import gov.nasa.pds.entities.Mission;
import gov.nasa.pds.entities.Personnel;
import gov.nasa.pds.entities.Product;
import gov.nasa.pds.entities.Property;
import gov.nasa.pds.entities.Reference;
import gov.nasa.pds.entities.Row;
import gov.nasa.pds.entities.Table;
import gov.nasa.pds.entities.Target;
import gov.nasa.pds.entities.TargetType;
import gov.nasa.pds.entities.Volume;
import gov.nasa.pds.services.ConversionPersistence;
import gov.nasa.pds.services.DataSetProcessingException;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

import com.topcoder.util.log.Log;

/**
 * <p>
 * The persistence service implementation used during the conversion process. Used to insert converted data.
 * </p>
 *
 * <p>
 * <b>Thread Safety:</b> The implementations are effectively thread-safe.
 * </p>
 *
 * @author argolite, TCSASSEMBLER
 * @version 1.0
 */
public class JDBCConversionPersistence implements ConversionPersistence, InitializingBean {
    /**
     * <p>
     * Represents the class name.
     * </p>
     */
    private static final String CLASS_NAME = JDBCConversionPersistence.class.getName();

    /**
     * Represents the JdbcTemplate instance used for all DB interaction.
     */
    private JdbcTemplate jdbcTemplate;

    /**
     * Represents the log instance used for logging.
     */
    private Log logger;

    /**
     * The default do nothing constructor.
     */
    public JDBCConversionPersistence() {
    }

    /**
     * Sets the JdbcTemplate instance used for all DB interaction.
     * @param jdbcTemplate the JdbcTemplate instance used for all DB interaction
     */
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * Sets the log instance used for logging.
     * @param logger the log instance used for logging
     */
    public void setLogger(Log logger) {
        this.logger = logger;
    }

    /**
     * Checks whether this class was initialized by Spring properly.
     *
     * @throws DataSetProcessingException if logger or jdbcTemplate are null
     */
    @Override
    public void afterPropertiesSet() throws DataSetProcessingException {
        Helper.checkNotNull("jdbcTemplate", jdbcTemplate);
        Helper.checkNotNull("logger", logger);
    }

    /**
     * Creates the table structure based on the metadata found in the Table, including its columns.
     *
     * @param table the given table instance
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public void createTableStructure(Table table) throws DataSetProcessingException {
        String name = CLASS_NAME + ".createTableStructure(Table table)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"table"},
                new Object[] {table});
        StringBuilder sql = new StringBuilder();
        sql.append("create table ").append(table.getName()).append(" (");
        int index = 0;
        for (Column column : table.getColumns()) {
            if (index != 0) {
                sql.append(", ");
            }
            sql.append(column.getName()).append(" ").append("VARCHAR");
            sql.append("(").append(column.getSize()).append(")");
            index++;
        }
        sql.append(")");
        try {
            jdbcTemplate.execute(sql.toString());
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }

        Helper.logMethodExitAndOutputParameters(logger, name, false, null);
    }

    /**
     * Inserts the given data into the given table into persistence.
     *
     * @param table the given table instance
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public void insertDataIntoTable(Table table) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertDataIntoTable(Table table)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"table"},
                new Object[] {table});
        StringBuilder sql = new StringBuilder();
        for (Row row : table.getRows()) {
            sql.append("insert into ").append(table.getName()).append(" (");
            int firstCell = 0;
            for (Cell cell : row.getCells()) {
                if (firstCell != 0) {
                    sql.append(", ");
                }
                sql.append(cell.getColumn().getName());
                firstCell++;
            }
            sql.append(")");
            sql.append(" values (");
            for (int i = 0; i < firstCell; i++) {
                if (i != 0) {
                    sql.append(", ");
                }
                sql.append("?");
            }
            sql.append(")");

            List<Object> values = new ArrayList<Object>();
            for (Cell cell : row.getCells()) {
                values.add(cell.getValue());
            }
            try {
                jdbcTemplate.update(sql.toString(), values.toArray());
            } catch (DataAccessException e) {
                Helper.logException(logger, name, e);
                throw new DataSetProcessingException("Data access error occurs", e);
            }
        }
        Helper.logMethodExitAndOutputParameters(logger, name, false, null);
    }

    /**
     * Inserts the given object into persistence.
     *
     * @param volume the given volume instance
     * @return the id of volume
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public long insertVolume(Volume volume) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertVolume(Volume volume)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"volume"},
                new Object[] {volume});
        try {
            long volumnId = Helper.insert(jdbcTemplate, "insert into volume (name, description, volume_text_id,"
                    + " volume_set_text_id, volume_set_name, volume_series_name) values (?, ?, ?, ?, ?, ?)",
                    new Object[] {volume.getName(), volume.getDescription(), volume.getTextId(),
                            volume.getSetTextId(), volume.getSetName(), volume.getSeriesName()});
            volume.setId(volumnId);
            insertOtherChildren(volume.getOtherChildren(), volumnId, "volume");
            insertImmediateProperties(volume.getOtherProperties(), volumnId, "volume");

            Helper.logMethodExitAndOutputParameters(logger, name, true, volumnId);
            return volumnId;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Inserts the given object into persistence.
     *
     * @param reference the given reference instance
     * @return the id of reference
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public long insertReference(Reference reference) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertReference(Reference reference)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"reference"},
                new Object[] {reference});
        try {
            long referenceId =  Helper.insert(jdbcTemplate, "insert into reference (reference_key_text_id, description)"
                    + " values (?, ?)",
                    new Object[] {reference.getKeyTextId(), reference.getDescription()});
            reference.setId(referenceId);
            Helper.logMethodExitAndOutputParameters(logger, name, true, referenceId);
            return referenceId;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Inserts the given object into persistence.
     *
     * @param instrumentHost the given instrumentHost instance
     * @return the id of instrumentHost
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public long insertInstrumentHost(InstrumentHost instrumentHost) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertInstrumentHost(InstrumentHost instrumentHost)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"instrumentHost"},
                new Object[] {instrumentHost});
        try {
            long instrumentHostId = Helper.insert(jdbcTemplate,
                    "insert into instrument_host (instrument_host_text_id, name)"
                    + " values (?, ?)",
                    new Object[] {instrumentHost.getTextId(), instrumentHost.getName()});
            instrumentHost.setId(instrumentHostId);
            insertReferences(instrumentHost.getReferences(), instrumentHostId, "instrument_host");
            insertOtherChildren(instrumentHost.getOtherChildren(), instrumentHostId, "instrument_host");
            Helper.logMethodExitAndOutputParameters(logger, name, true, instrumentHostId);
            return instrumentHostId;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Inserts the given object into persistence.
     *
     * @param instrument the given instrument instance
     * @return the id of instrument
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public long insertInstrument(Instrument instrument) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertInstrument(Instrument instrument)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"instrument"},
                new Object[] {instrument});
        try {
            long instrumentId = Helper.insert(jdbcTemplate,
                    "insert into instrument (instrument_text_id, name, type, description)"
                    + " values (?, ?, ?, ?)",
                    new Object[] {instrument.getTextId(), instrument.getName(), instrument.getType(),
                        instrument.getDescription()});
            instrument.setId(instrumentId);
            insertReferences(instrument.getReferences(), instrumentId, "instrument");
            insertOtherChildren(instrument.getOtherChildren(), instrumentId, "instrument");
            if (instrument.getHosts() != null) {
                for (InstrumentHost instrumentHost : instrument.getHosts()) {
                    jdbcTemplate.update(
                        "insert into instrument_host_instrument (instrument_host_id, instrument_id) values (?, ?)",
                        new Object[] {instrumentHost.getId(), instrumentId});
                }
            }
            Helper.logMethodExitAndOutputParameters(logger, name, true, instrumentId);
            return instrumentId;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Inserts the given object into persistence.
     *
     * @param mission the given mission instance
     * @return the id of mission
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public long insertMission(Mission mission) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertMission(Mission mission)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"mission"},
                new Object[] {mission});
        try {
            long missionId = Helper.insert(jdbcTemplate, "insert into mission (name, start_date, end_date, description)"
                    + " values (?, ?, ?, ?)",
                    new Object[] {mission.getName(), mission.getStartDate(), mission.getEndDate(),
                        mission.getDescription()});
            mission.setId(missionId);
            insertReferences(mission.getReferences(), missionId, "mission");
            insertOtherChildren(mission.getOtherChildren(), missionId, "mission");
            Helper.logMethodExitAndOutputParameters(logger, name, true, missionId);
            return missionId;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Inserts the given object into persistence.
     *
     * @param target the given target instance
     * @return the id of target
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public long insertTarget(Target target) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertTarget(Target target)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"target"},
                new Object[] {target});
        try {
            long targetId =  Helper.insert(jdbcTemplate, "insert into target (name) values (?)",
                    new Object[] {target.getName()});
            target.setId(targetId);
            insertReferences(target.getReferences(), targetId, "target");
            if (target.getTypes() != null) {
                for (TargetType targetType : target.getTypes()) {
                    Helper.insert(jdbcTemplate,
                            "insert into target_type_target (target_type_id, target_id) values (?, ?)",
                            new Object[] {targetType.getId(), targetId});
                }
            }
            Helper.logMethodExitAndOutputParameters(logger, name, true, targetId);
            return targetId;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }
    
    /**
     * Inserts the given object into persistence.
     * 
     * @param personnel
     *            - the given personnel instance.
     * @throws DataSetProcessingException
     *             - if there is an error while persisting the data
     * @return - the id of personnel.
     */
    public long insertPersonnel(Personnel personnel) throws DataSetProcessingException {
	    // TODO Not implemented
	    
	    return 0L;
	}

    /**
     * Inserts the given object into persistence.
     *
     * @param dataSet the given dataSet instance
     * @return the id of dataSet
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public long insertDataSet(DataSet dataSet) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertDataSet(DataSet dataSet)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"dataSet"},
                new Object[] {dataSet});
        try {
            long dataSetId =  Helper.insert(jdbcTemplate,
                    "insert into dataset (data_set_text_id, name, start_time, stop_time,"
                    + " description) values (?, ?, ?, ?, ?)",
                    new Object[] {dataSet.getTextId(), dataSet.getName(), dataSet.getStartDate(),
                        dataSet.getStopDate(), dataSet.getDescription()});
            dataSet.setId(dataSetId);
            if (dataSet.getInstruments() != null) {
                for (Instrument instrument : dataSet.getInstruments()) {
                    Helper.insert(jdbcTemplate, "insert into instrument_catalog (dataset_id, instrument_id)"
                            + " values (?, ?)",
                            new Object[] {dataSetId, instrument.getId()});
                }
            }

            if (dataSet.getMissions() != null) {
                for (Mission mission : dataSet.getMissions()) {
                    Helper.insert(jdbcTemplate, "insert into dataset_mission (dataset_id, mission_id)"
                            + " values (?, ?)",
                            new Object[] {dataSetId, mission.getId()});
                }
            }

            insertOtherChildren(dataSet.getOtherChildren(), dataSetId, "dataset");

            if (dataSet.getRating() != null) {
                Helper.insert(jdbcTemplate, "insert into dataset_rating (rating, dataset_id) values (?, ?)",
                        new Object[] {dataSet.getRating(), dataSetId});
            }

            if (dataSet.getReferences() != null) {
                for (Reference reference : dataSet.getReferences()) {
                    jdbcTemplate.update(
                            "insert into reference_catalog (dataset_id, reference_id) values (?, ?)",
                            new Object[] {dataSetId, reference.getId()});
                }
            }

            if (dataSet.getTargets() != null) {
                for (Target target : dataSet.getTargets()) {
                    Helper.insert(jdbcTemplate, "insert into dataset_target (dataset_id, target_id)"
                            + " values (?, ?)",
                            new Object[] {dataSetId, target.getId()});
                }
            }

            if (dataSet.getVolumes() != null) {
                for (Volume volume : dataSet.getVolumes()) {
                    Helper.insert(jdbcTemplate, "insert into dataset_volume (dataset_id, volume_id)"
                            + " values (?, ?)",
                            new Object[] {dataSetId, volume.getId()});
                }
            }
            Helper.logMethodExitAndOutputParameters(logger, name, true, dataSetId);
            return dataSetId;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Inserts the given object into persistence.
     *
     * @param product the given product instance
     * @param dataSetId the given dataSetId value
     * @return the id of product
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public long insertProduct(long dataSetId, Product product) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertProduct(long dataSetId, Product product)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"dataSetId", "product"},
                new Object[] {dataSetId, product});
        try {
            long productId = Helper.insert(jdbcTemplate,
                    "insert into product (name, product_text_id, start_time, stop_time,"
                    + " description, record_type, record_byte_size, record_count) values (?, ?, ?, ?, ?, ?, ?, ?)",
                    new Object[] {product.getName(), product.getTextId(), product.getStartTime(),
                        product.getStopTime(), product.getDescription(), product.getRecordType().name(),
                        product.getRecordByteSize(), product.getRecordCount()});
            product.setId(productId);

            Helper.insert(jdbcTemplate, "insert into product_index (dataset_id, data_product_id) values (?, ?)",
                    new Object[] {dataSetId, productId});

            if (product.getAssociatedFiles() != null) {
                for (DataFile dataFile : product.getAssociatedFiles()) {
                    Helper.insert(jdbcTemplate, "insert into product_file (product_id, data_file_id) values (?, ?)",
                            new Object[] {productId, dataFile.getId()});
                }
            }

            if (product.getAssociatedTableName() != null) {
                associateTableToProduct(productId, product.getAssociatedTableName());
            }

            insertOtherChildren(product.getOtherChildren(), productId, "product");
            insertImmediateProperties(product.getOtherProperties(), productId, "product");
            Helper.logMethodExitAndOutputParameters(logger, name, true, productId);
            return productId;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Inserts the given object into persistence.
     *
     * @param dataFile the given dataFile instance
     * @param productId the given productId value
     * @return the id of product file
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public long insertProductDocument(long productId, DataFile dataFile) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertProductDocument(long productId, DataFile dataFile)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"productId", "dataFile"},
                new Object[] {productId, dataFile});
        try {
            long dataFileId = Helper.insert(jdbcTemplate,
                    "insert into data_file (name, path, content) values (?, ?, ?)",
                    new Object[] {dataFile.getName(), dataFile.getPath(), dataFile.getContent()});
            dataFile.setId(dataFileId);
            long productFileId = Helper.insert(jdbcTemplate,
                    "insert into product_file (product_id, data_file_id) values (?, ?)",
                    new Object[] {productId, dataFile.getId()});
            Helper.logMethodExitAndOutputParameters(logger, name, true, productFileId);
            return productFileId;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Inserts the given object into persistence.
     *
     * @param dataSetId the given dataSetId value
     * @param dataFile the given dataFile instance
     * @return the id of dataset file
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public long insertDataSetDocument(long dataSetId, DataFile dataFile) throws DataSetProcessingException {
        String name = CLASS_NAME + ".insertDataSetDocument(long dataSetId, DataFile dataFile)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"dataSetId", "dataFile"},
                new Object[] {dataSetId, dataFile});
        try {
            long dataFileId = Helper.insert(jdbcTemplate,
                    "insert into data_file (name, path, content) values (?, ?, ?)",
                    new Object[] {dataFile.getName(), dataFile.getPath(), dataFile.getContent()});
            dataFile.setId(dataFileId);
            long datasetFileId = Helper.insert(jdbcTemplate,
                    "insert into dataset_file (dataset_id, data_file_id) values (?, ?)",
                    new Object[] {dataSetId, dataFileId});
            Helper.logMethodExitAndOutputParameters(logger, name, true, datasetFileId);
            return datasetFileId;
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
    }

    /**
     * Associates the table with the given name to the product with the given ID.
     *
     * @param dataTableName the given dataTableName value
     * @param productId the given productId value
     * @throws DataSetProcessingException if there is an error while persisting the data
     */
    public void associateTableToProduct(long productId, String dataTableName) throws DataSetProcessingException {
        String name = CLASS_NAME + ".associateTableToProduct(long productId, String dataTableName)";
        Helper.logMethodEntryAndInputParameters(logger, name, new String[] {"productId", "dataTableName"},
                new Object[] {productId, dataTableName});
        try {
            jdbcTemplate.update("insert into product_table (product_id, data_table_name) values (?, ?)",
                    new Object[] {productId, dataTableName});
        } catch (DataAccessException e) {
            Helper.logException(logger, name, e);
            throw new DataSetProcessingException("Data access error occurs", e);
        }
        Helper.logMethodExitAndOutputParameters(logger, name, false, null);
    }

    /**
     * Inserts other children and their children and properties.
     *
     * @param objects the meta data object children
     * @param id the id
     * @param type represents the related table name
     */
    private void insertOtherChildren(List<MetadataObject> objects, long id, String type) {
        if (objects == null) {
            return;
        }
        for (MetadataObject object : objects) {
            insertMetadata(object, id, null, type);
        }
    }

    /**
     * Inserts each meta data object and their properties.
     *
     * @param object the meta data object
     * @param id the id
     * @param parentId the id of parent meta data object
     * @param type represents the related table name
     */
    private void insertMetadata(MetadataObject object, long id, Long parentId, String type) {
        Long keywordId = Helper.getKeywordId(object.getName(), jdbcTemplate);
        long lookupValueId = Helper.insert(jdbcTemplate,
                "insert into lookup_value (keyword_id, value, is_property) values (?, ?, ?)",
                new Object[] {keywordId, null, false});
        object.setId(lookupValueId);
        if (parentId == null) {
            StringBuilder sql = new StringBuilder();
            sql.append("insert into ").append(type).append("_metadata (").append(type)
                .append("_id, lookup_value_id) values (?, ?)");
            long objectId = Helper.insert(jdbcTemplate, sql.toString(), new Object[] {id, lookupValueId});
            object.setId(objectId);
        } else {
            jdbcTemplate.update("insert into lookup_value_xref (parent_id, child_id) values (?, ?)",
                    new Object[] {parentId, lookupValueId});
        }

        insertProperties(object.getProperties(), lookupValueId, type);

        if (object.getChildren() == null) {
            return;
        }
        for (MetadataObject child : object.getChildren()) {
            insertMetadata(child, id, lookupValueId, type);
        }
    }

    /**
     * Inserts the properties which are from a meta data object.
     *
     * @param properties the properties
     * @param parentId the id of parent meta data object
     * @param type represents the related table name
     */
    private void insertProperties(List<Property> properties, Long parentId, String type) {
        if (properties == null) {
            return;
        }
        long lookupValueId;
        for (Property property : properties) {
            Long keywordId = Helper.getKeywordId(property.getName(), jdbcTemplate);
            for (String value : property.getValues()) {
                lookupValueId = Helper.insert(jdbcTemplate,
                        "insert into lookup_value (keyword_id, value, is_property) values (?, ?, ?)",
                        new Object[] {keywordId, value, true});
                property.setId(lookupValueId);
                jdbcTemplate.update("insert into lookup_value_xref (parent_id, child_id) values (?, ?)",
                        new Object[] {parentId, lookupValueId});
            }
        }
    }

    /**
     * Inserts the immediate properties which are from the non meta data object, eg. volume.
     *
     * @param properties the properties
     * @param id the id of the non meta data object, eg. volume
     * @param type represents the related table name
     */
    private void insertImmediateProperties(List<Property> properties, long id, String type) {
        if (properties == null) {
            return;
        }
        long lookupValueId;
        for (Property property : properties) {
            Long keywordId = Helper.getKeywordId(property.getName(), jdbcTemplate);
            for (String value : property.getValues()) {
                lookupValueId = Helper.insert(jdbcTemplate,
                        "insert into lookup_value (keyword_id, value, is_property) values (?, ?, ?)",
                        new Object[] {keywordId, value, true});
                property.setId(lookupValueId);
                StringBuilder sql = new StringBuilder();
                sql.append("insert into ").append(type).append("_metadata (").append(type)
                    .append("_id, lookup_value_id) values (?, ?)");
                Helper.insert(jdbcTemplate, sql.toString(), new Object[] {id, lookupValueId});
            }
        }
    }

    /**
     * Inserts the references for specified type.
     *
     * @param references the references
     * @param id the id
     * @param type represents the related table name
     */
    private void insertReferences(List<Reference> references, long id, String type) {
        if (references == null) {
            return;
        }
        for (Reference reference : references) {
            StringBuilder sql = new StringBuilder();
            sql.append("insert into ").append(type).append("_reference (").append(type)
                .append("_id, reference_id) values (?, ?)");
            jdbcTemplate.update(
                    sql.toString(),
                    new Object[] {id, reference.getId()});
        }
    }
}